/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.childcare.dao;

import com.aptech.childcare.entities.Child;
import com.aptech.childcare.until.ConfigDatabase;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author BaoTien
 */
public class ChildDAO {

    private String lastError;
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ConfigDatabase db = null;
    final String sqlCREATE = "INSERT INTO tbChild VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    final String sqlREAD = "SELECT * FROM tbChild";
    final String sqlREAD1 = "SELECT * FROM tbChild Where ClassID = ?";
    final String sqlDELETE = "DELETE FROM tbChild WHERE ChildID = ?";
    final String sqlUPDATE = "SELECT * FROM tbChild WHERE ChildID = ?";

    /**
     *
     */
    public ChildDAO() {
        db = new ConfigDatabase();
    }

    /**
     *
     * @return list of Child in database
     */
    public ArrayList<Child> readByAll() throws IOException {
        ArrayList<Child> list = new ArrayList<Child>();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlREAD);
            rs = pst.executeQuery();
            while (rs.next()) {
                Child chi = new Child();
                chi.setChildID(rs.getInt(1));
                chi.setClassID(rs.getInt(2));
                chi.setLastName(rs.getString(3));
                chi.setFirstName(rs.getString(4));
                chi.setSex(rs.getString(5));
                chi.setPathImage(rs.getString(6));
                chi.setBirthday(rs.getDate(7));
                chi.setAddress(rs.getString(8));
                chi.setCurrentMedication(rs.getString(9));
                chi.setPasrIllness(rs.getString(10));
                chi.setDoctorName(rs.getString(11));
                chi.setDoctorPhone(rs.getString(12));
                chi.setParentName(rs.getString(13));
                chi.setParentWorkNumber(rs.getString(14));
                chi.setParentPhone(rs.getString(15));
                chi.setParentEmail(rs.getString(16));
                chi.setNextOfKinContact(rs.getString(17));
                chi.setRegistrationDate(rs.getDate(18));
                chi.setDateReceived(rs.getDate(19));
                list.add(chi);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    public ArrayList<Child> readByAllIDChild(int ClassID) throws IOException {
        ArrayList<Child> list = new ArrayList<Child>();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlREAD1);
            pst.setInt(1, ClassID);
            rs = pst.executeQuery();
            while (rs.next()) {
                Child chi = new Child();
                chi.setChildID(rs.getInt(1));
                chi.setClassID(rs.getInt(2));
                chi.setLastName(rs.getString(3));
                chi.setFirstName(rs.getString(4));
                chi.setSex(rs.getString(5));
                chi.setPathImage(rs.getString(6));
                chi.setBirthday(rs.getDate(7));
                chi.setAddress(rs.getString(8));
                chi.setCurrentMedication(rs.getString(9));
                chi.setPasrIllness(rs.getString(10));
                chi.setDoctorName(rs.getString(11));
                chi.setDoctorPhone(rs.getString(12));
                chi.setParentName(rs.getString(13));
                chi.setParentWorkNumber(rs.getString(14));
                chi.setParentPhone(rs.getString(15));
                chi.setParentEmail(rs.getString(16));
                chi.setNextOfKinContact(rs.getString(17));
                chi.setRegistrationDate(rs.getDate(18));
                chi.setDateReceived(rs.getDate(19));
                list.add(chi);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    /**
     *
     * @param chi needed check
     * @return true if not exist, false if exist
     */
    public boolean check(Child chi) throws IOException {
        ArrayList<Child> list = readByAll();
        for (Child ch : list) {
            if (ch.getLastName().equals(chi.getLastName()) && ch.getFirstName().equals(chi.getFirstName())) {
                setLastError("Child existed");
                return false;
            }
        }
        return true;
    }

    /**
     *
     * @param chi the Child to add
     * @return true if add successful, false if add fail
     */
    public boolean create(Child chi) throws IOException {
        if (!check(chi)) {
            return false;
        }
        try {
            con = db.getConnection();
            pst = con.prepareStatement(sqlCREATE);
            pst.setInt(1, chi.getClassID());
            pst.setString(2, chi.getLastName());
            pst.setString(3, chi.getFirstName());
            pst.setString(4, chi.getSex());
            pst.setString(5, chi.getPathImage());
            pst.setDate(6, chi.getBirthday());
            pst.setString(7, chi.getAddress());
            pst.setString(8, chi.getCurrentMedication());
            pst.setString(9, chi.getPasrIllness());
            pst.setString(10, chi.getDoctorName());
            pst.setString(11, chi.getDoctorPhone());
            pst.setString(12, chi.getParentName());
            pst.setString(13, chi.getParentWorkNumber());
            pst.setString(14, chi.getParentPhone());
            pst.setString(15, chi.getParentEmail());
            pst.setString(16, chi.getNextOfKinContact());
            pst.setDate(17, chi.getRegistrationDate());
            pst.setDate(18, chi.getDateReceived());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Add fail!");
        return false;
    }

    /**
     *
     * @param chi the Child to edit
     * @return true if edit successful, false if edit fail
     */
    public boolean update(Child chi) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, chi.getChildID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateInt(2, chi.getClassID());
            rs.updateString(3, chi.getLastName());
            rs.updateString(4, chi.getFirstName());
            rs.updateString(5, chi.getSex());
            rs.updateDate(7, chi.getBirthday());
            rs.updateString(8, chi.getAddress());
            rs.updateString(9, chi.getCurrentMedication());
            rs.updateString(10, chi.getPasrIllness());
            rs.updateString(11, chi.getDoctorName());
            rs.updateString(12, chi.getDoctorPhone());
            rs.updateString(13, chi.getParentName());
            rs.updateString(14, chi.getParentWorkNumber());
            rs.updateString(15, chi.getParentPhone());
            rs.updateString(16, chi.getParentEmail());
            rs.updateString(17, chi.getNextOfKinContact());
            rs.updateDate(18, chi.getRegistrationDate());
            rs.updateDate(19, chi.getDateReceived());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            db.closeConnection(db);
        }
        //setLastError("Update Fail !");
        return false;
    }

    /**
     *
     * @param ChildID the Child to delete
     * @return true if delete successful, false if delete fail.
     */
    public boolean delete(int ChildID) throws IOException {
        try {
            con = db.getConnection();
            pst = con.prepareStatement(sqlDELETE);
            pst.setInt(1, ChildID);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL fail!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Delete fail!");
        return false;
    }

    public ArrayList<Child> readByName(String Name) throws IOException {
        ArrayList<Child> list = readByAll();
        ArrayList<Child> chList = new ArrayList<Child>();
        for (Child ch : list) {
            if (ch.getLastName().toLowerCase().matches(".*" + Name.trim().toLowerCase() + ".*") || ch.getFirstName().toLowerCase().matches(".*" + Name.trim().toLowerCase() + ".*")) {
                chList.add(ch);
            }
        }
        return chList;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
